This Python Jupyter notebook can parse watercare bills and extract the usage data. It can be used to generate plots of the usage data.

You can download this notebook from https://github.com/neon-ninja/watercare/blob/main/parse.ipynb

You can download PDFs from https://myaccount.watercare.co.nz/bills-and-payments. Set your start date filter as far back as it'll go, and download all the PDFs into a folder called pdfs. Here's a JS snippet you can put in your browser console to save you clicking them all:

var links = document.querySelectorAll('button.flex.hover\\:bg-blue-100')
console.log(`Found ${links.length} links`)
var index = 0
setInterval(function() {
    if (index >= links.length) {
        return
    }
    console.log(index)
    links[index].click()
    index++
}, 250)
In [1]:
import pandas as pd # Tabular data
pd.options.plotting.backend = "plotly"
import plotly.express as px # Plotting
from glob import glob # Finding files
import pdftotext # PDF parsing
from tqdm.auto import tqdm # Progress bars
import re # Regular expressions
from datetime import datetime
pd.set_option("display.max_columns", None)
pd.set_option("display.max_rows", None)

files = sorted(glob("pdfs/*.pdf"))
len(files)
Out[1]:
64
In [2]:
def read_PDF(filename):
    with open(filename, "rb") as f:
        pdf = pdftotext.PDF(f)
        text = ""
        for page_text in pdf:
            text += page_text.strip()
        return text

def parse_number(amount):
    if amount.endswith(" cr"):
        return -float(amount[:-3])
    return float(amount)

def parse_text(text):
    if "$500 gift card" in text:
        # This breaks the dollar_amounts regex
        text = text.replace("$ 50", "")
    dates = re.findall(r'(\d{2} [A-Z][a-z]{2} \d{4})', text)
    dollar_amounts = [parse_number(d) for d in re.findall(r'\$ ([\d\.]+(?: cr)?)', text)]
    usage = [parse_number(d) for d in re.findall(r'([\d\.]+) kL', text)]
    unit_rates = [parse_number(d) for d in re.findall(r'\$([\d\.]+)/kL', text)]
    if len(unit_rates) == 4:
        # Rate changed
        unit_rates = unit_rates[::2]

    consumption_period = re.search(r"Consumption period (\d+) days", text).group(1)
    this_reading_match = re.search(r"This reading\s+(\d{2}-\w{3}-\d{2})\s+(\d+)\s*(Estimate|Actual)?", text)
    this_reading_date = this_reading_match.group(1)
    this_reading_date = datetime.strptime(this_reading_date, "%d-%b-%y")
    this_reading_value = int(this_reading_match.group(2))
    this_reading_type = this_reading_match.group(3)

    return {
        "Invoice date": dates[0],
        "Due date": dates[1],
        "Total due ($)": dollar_amounts[0],
        "Opening balance ($)": dollar_amounts[1],
        "Payments received ($)": dollar_amounts[2],
        "Balance still owing ($)": dollar_amounts[3],
        "Water consumption ($)": dollar_amounts[4],
        "Wastewater consumption ($)": dollar_amounts[5],
        "Wastewater fixed ($)": dollar_amounts[6],
        "Balance of current charges ($)": dollar_amounts[7],
        "GST ($)": dollar_amounts[8],
        "Consumption period (days)": int(consumption_period),
        "Reading date": this_reading_date,
        "Reading value (kL)": this_reading_value,
        "Reading type": this_reading_type,
        "Water consumption (kL)": usage[0],
        "Wastewater consumption (kL)": usage[1],
        "Wastewater rate (%)": float(re.search(r"@(\d+.\d+)%", text).group(1)),
        "Water unit rate ($/kL)": unit_rates[0],
        "Wastewater unit rate ($/kL)": unit_rates[1],
    }

results = []
for f in tqdm(files):
    text = read_PDF(f)
    results.append(parse_text(text))
df = pd.DataFrame(results)
for col in ['Invoice date', 'Due date', 'Reading date']:
    df[col] = pd.to_datetime(df[col])
df.sort_values("Invoice date", inplace=True, ascending=False)
  0%|          | 0/64 [00:00<?, ?it/s]
In [3]:
df.style.background_gradient(cmap="RdYlGn_r").apply(lambda x: ["background: red" if v == "Estimate" else "background: green" for v in x], subset=["Reading type"])
Out[3]:
  Invoice date Due date Total due ($) Opening balance ($) Payments received ($) Balance still owing ($) Water consumption ($) Wastewater consumption ($) Wastewater fixed ($) Balance of current charges ($) GST ($) Consumption period (days) Reading date Reading value (kL) Reading type Water consumption (kL) Wastewater consumption (kL) Wastewater rate (%) Water unit rate ($/kL) Wastewater unit rate ($/kL)
0 2024-09-06 00:00:00 2024-09-27 00:00:00 484.900000 40.240000 -40.240000 0.000000 192.780000 263.240000 28.880000 484.900000 63.250000 34 2024-09-03 00:00:00 1200 Actual 90.000000 70.650000 78.500000 2.142000 3.726000
63 2024-07-31 00:00:00 2024-08-21 00:00:00 40.240000 -2.960000 0.000000 -2.960000 8.570000 11.700000 22.930000 43.200000 5.630000 27 2024-07-31 00:00:00 1110 Estimate 4.000000 3.140000 78.500000 2.142000 3.726000
11 2024-07-05 00:00:00 2024-07-26 00:00:00 0.000000 -54.220000 0.000000 -54.220000 9.990000 13.630000 27.640000 51.260000 6.690000 35 2024-07-04 00:00:00 1106 Actual 5.000000 3.920000 78.500000 1.998000 3.476000
22 2024-05-30 00:00:00 2024-06-20 00:00:00 0.000000 -86.570000 0.000000 -86.570000 5.990000 8.200000 18.160000 32.350000 4.220000 23 2024-05-30 00:00:00 1101 Estimate 3.000000 2.360000 78.500000 1.998000 3.476000
33 2024-05-08 00:00:00 2024-05-29 00:00:00 0.000000 45.730000 -45.730000 0.000000 -49.950000 -68.200000 31.580000 -86.570000 -11.290000 40 2024-05-07 00:00:00 1098 Actual 25.000000 19.620000 78.500000 1.998000 3.476000
44 2024-03-28 00:00:00 2024-04-18 00:00:00 45.730000 74.910000 -74.910000 0.000000 11.990000 16.370000 17.370000 45.730000 5.960000 22 2024-03-28 00:00:00 1123 Estimate 6.000000 4.710000 78.500000 1.998000 3.476000
55 2024-03-07 00:00:00 2024-03-28 00:00:00 74.910000 67.790000 -67.790000 0.000000 19.980000 27.290000 27.640000 74.910000 9.770000 35 2024-03-06 00:00:00 1117 Estimate 10.000000 7.850000 78.500000 1.998000 3.476000
60 2024-01-31 00:00:00 2024-02-21 00:00:00 67.790000 66.210000 -66.210000 0.000000 17.980000 24.540000 25.270000 67.790000 8.840000 32 2024-01-31 00:00:00 1107 Estimate 9.000000 7.060000 78.500000 1.998000 3.476000
61 2024-01-03 00:00:00 2024-01-24 00:00:00 66.210000 59.920000 -59.920000 0.000000 17.980000 24.540000 23.690000 66.210000 8.640000 30 2023-12-30 00:00:00 1098 Estimate 9.000000 7.060000 78.500000 1.998000 3.476000
62 2023-11-30 00:00:00 2023-12-21 00:00:00 59.920000 74.120000 -74.120000 0.000000 15.980000 21.830000 22.110000 59.920000 7.820000 28 2023-11-30 00:00:00 1089 Estimate 8.000000 6.280000 78.500000 1.998000 3.476000
1 2023-11-03 00:00:00 2023-11-24 00:00:00 74.120000 60.710000 -60.710000 0.000000 19.980000 27.290000 26.850000 74.120000 9.670000 34 2023-11-02 00:00:00 1081 Estimate 10.000000 7.850000 78.500000 1.998000 3.476000
2 2023-09-29 00:00:00 2023-10-20 00:00:00 60.710000 57.590000 -57.590000 0.000000 15.980000 21.830000 22.900000 60.710000 7.920000 29 2023-09-29 00:00:00 1071 Estimate 8.000000 6.280000 78.500000 1.998000 3.476000
3 2023-09-01 00:00:00 2023-09-22 00:00:00 57.590000 70.650000 -70.650000 0.000000 13.990000 19.120000 24.480000 57.590000 7.510000 31 2023-08-31 00:00:00 1063 Actual 7.000000 5.500000 78.500000 1.998000 3.476000
4 2023-07-31 00:00:00 2023-08-21 00:00:00 70.650000 60.530000 -60.530000 0.000000 20.080000 27.420000 23.150000 70.650000 9.220000 32 2023-07-31 00:00:00 1056 Estimate 11.000000 8.640000 78.500000 1.825000 3.174000
5 2023-07-03 00:00:00 2023-07-24 00:00:00 60.530000 74.220000 -74.220000 0.000000 16.420000 22.410000 21.700000 60.530000 7.900000 30 2023-06-29 00:00:00 1045 Actual 9.000000 7.060000 78.500000 1.825000 3.174000
6 2023-05-30 00:00:00 2023-06-20 00:00:00 74.220000 85.750000 -85.750000 0.000000 21.900000 29.900000 22.420000 74.220000 9.680000 31 2023-05-30 00:00:00 1036 Estimate 12.000000 9.420000 78.500000 1.825000 3.174000
7 2023-05-01 00:00:00 2023-05-22 00:00:00 85.750000 61.250000 -61.250000 0.000000 27.380000 37.390000 20.980000 85.750000 11.180000 29 2023-04-29 00:00:00 1024 Actual 15.000000 11.780000 78.500000 1.825000 3.174000
8 2023-03-31 00:00:00 2023-04-21 00:00:00 61.250000 37.520000 -37.520000 0.000000 16.420000 22.410000 22.420000 61.250000 7.990000 31 2023-03-31 00:00:00 1009 Estimate 9.000000 7.060000 78.500000 1.825000 3.174000
9 2023-03-01 00:00:00 2023-03-22 00:00:00 37.520000 86.470000 -86.470000 0.000000 7.300000 9.970000 20.250000 37.520000 4.890000 28 2023-02-28 00:00:00 1000 Actual 4.000000 3.140000 78.500000 1.825000 3.174000
10 2023-01-31 00:00:00 2023-02-21 00:00:00 86.470000 66.310000 -66.310000 0.000000 25.550000 34.880000 26.040000 86.470000 11.280000 36 2023-01-31 00:00:00 996 Estimate 14.000000 10.990000 78.500000 1.825000 3.174000
12 2023-01-04 00:00:00 2023-01-25 00:00:00 66.310000 63.420000 -63.420000 0.000000 20.080000 27.420000 18.810000 66.310000 8.650000 26 2022-12-26 00:00:00 982 Actual 11.000000 8.640000 78.500000 1.825000 3.174000
13 2022-11-30 00:00:00 2022-12-21 00:00:00 63.420000 71.370000 -71.370000 0.000000 18.250000 24.920000 20.250000 63.420000 8.270000 28 2022-11-30 00:00:00 971 Estimate 10.000000 7.850000 78.500000 1.825000 3.174000
14 2022-11-03 00:00:00 2022-11-24 00:00:00 71.370000 68.480000 -68.480000 0.000000 20.080000 27.420000 23.870000 71.370000 9.310000 33 2022-11-02 00:00:00 961 Actual 11.000000 8.640000 78.500000 1.825000 3.174000
15 2022-09-30 00:00:00 2022-10-21 00:00:00 68.480000 54.830000 -54.830000 0.000000 20.080000 27.420000 20.980000 68.480000 8.930000 29 2022-09-30 00:00:00 950 Estimate 11.000000 8.640000 78.500000 1.825000 3.174000
16 2022-09-02 00:00:00 2022-09-23 00:00:00 54.830000 77.790000 -77.790000 0.000000 12.780000 17.460000 24.590000 54.830000 7.150000 34 2022-09-01 00:00:00 939 Actual 7.000000 5.500000 78.500000 1.825000 3.174000
17 2022-07-29 00:00:00 2022-08-19 00:00:00 77.790000 116.490000 -116.490000 0.000000 25.550000 34.880000 17.360000 77.790000 10.150000 24 2022-07-29 00:00:00 932 Estimate 14.000000 10.990000 78.500000 1.825000 3.174000
18 2022-07-06 00:00:00 2022-07-27 00:00:00 116.490000 70.700000 -70.700000 0.000000 39.240000 53.570000 23.680000 116.490000 15.190000 35 2022-07-05 00:00:00 918 Actual 23.000000 18.060000 78.500000 1.706000 2.966000
20 2022-05-31 00:00:00 2022-06-21 00:00:00 70.700000 99.670000 -99.670000 0.000000 22.180000 30.250000 18.270000 70.700000 9.220000 27 2022-05-31 00:00:00 895 Estimate 13.000000 10.200000 78.500000 1.706000 2.966000
19 2022-05-05 00:00:00 2022-05-26 00:00:00 99.670000 58.610000 -58.610000 0.000000 32.410000 44.250000 23.010000 99.670000 13.000000 34 2022-05-04 00:00:00 882 Actual 19.000000 14.920000 78.500000 1.706000 2.966000
21 2022-03-31 00:00:00 2022-04-21 00:00:00 58.610000 80.160000 -80.160000 0.000000 17.060000 23.280000 18.270000 58.610000 7.640000 27 2022-03-31 00:00:00 863 Estimate 10.000000 7.850000 78.500000 1.706000 2.966000
23 2022-03-07 00:00:00 2022-03-28 00:00:00 80.160000 42.460000 -42.460000 0.000000 23.880000 32.600000 23.680000 80.160000 10.460000 35 2022-03-04 00:00:00 853 Actual 14.000000 10.990000 78.500000 1.706000 2.966000
24 2022-01-28 00:00:00 2022-02-18 00:00:00 42.460000 74.800000 -74.800000 0.000000 11.940000 16.310000 14.210000 42.460000 5.540000 21 2022-01-28 00:00:00 839 Estimate 7.000000 5.500000 78.500000 1.706000 2.966000
25 2022-01-10 00:00:00 2022-02-01 00:00:00 74.800000 63.350000 -63.350000 0.000000 20.470000 27.940000 26.390000 74.800000 9.760000 39 2022-01-07 00:00:00 832 Actual 12.000000 9.420000 78.500000 1.706000 2.966000
26 2021-11-29 00:00:00 2021-12-20 00:00:00 63.350000 66.730000 -66.730000 0.000000 18.770000 25.630000 18.950000 63.350000 8.260000 28 2021-11-29 00:00:00 820 Estimate 11.000000 8.640000 78.500000 1.706000 2.966000
27 2021-11-02 00:00:00 2021-11-23 00:00:00 66.730000 72.730000 -72.730000 0.000000 18.770000 25.630000 22.330000 66.730000 8.700000 33 2021-11-01 00:00:00 809 Actual 11.000000 8.640000 78.500000 1.706000 2.966000
28 2021-09-29 00:00:00 2021-10-20 00:00:00 72.730000 74.080000 -74.080000 0.000000 22.180000 30.250000 20.300000 72.730000 9.490000 30 2021-09-29 00:00:00 798 Estimate 13.000000 10.200000 78.500000 1.706000 2.966000
30 2021-08-31 00:00:00 2021-09-21 00:00:00 74.080000 67.980000 -67.980000 0.000000 22.180000 30.250000 21.650000 74.080000 9.660000 32 2021-08-30 00:00:00 785 Estimate 13.000000 10.200000 78.500000 1.706000 2.966000
29 2021-07-29 00:00:00 2021-08-19 00:00:00 67.980000 80.570000 -80.570000 0.000000 20.720000 28.270000 18.990000 67.980000 8.870000 30 2021-07-29 00:00:00 772 Estimate 13.000000 10.200000 78.500000 1.594000 2.772000
31 2021-06-30 00:00:00 2021-07-21 00:00:00 80.570000 51.640000 -51.640000 0.000000 25.500000 34.820000 20.250000 80.570000 10.510000 32 2021-06-29 00:00:00 759 Actual 16.000000 12.560000 78.500000 1.594000 2.772000
32 2021-05-28 00:00:00 2021-06-18 00:00:00 51.640000 57.320000 -57.320000 0.000000 14.350000 19.570000 17.720000 51.640000 6.740000 28 2021-05-28 00:00:00 743 Estimate 9.000000 7.060000 78.500000 1.594000 2.772000
34 2021-05-03 00:00:00 2021-05-24 00:00:00 57.320000 61.730000 -61.730000 0.000000 15.940000 21.760000 19.620000 57.320000 7.480000 31 2021-04-30 00:00:00 734 Actual 10.000000 7.850000 78.500000 1.594000 2.772000
35 2021-03-30 00:00:00 2021-04-20 00:00:00 61.730000 51.640000 -51.640000 0.000000 17.530000 23.950000 20.250000 61.730000 8.050000 32 2021-03-30 00:00:00 724 Estimate 11.000000 8.640000 78.500000 1.594000 2.772000
36 2021-03-01 00:00:00 2021-03-22 00:00:00 51.640000 56.050000 -56.050000 0.000000 14.350000 19.570000 17.720000 51.640000 6.740000 28 2021-02-26 00:00:00 713 Actual 9.000000 7.060000 78.500000 1.594000 2.772000
37 2021-01-29 00:00:00 2021-02-19 00:00:00 56.050000 61.100000 -61.100000 0.000000 15.940000 21.760000 18.350000 56.050000 7.310000 29 2021-01-29 00:00:00 704 Estimate 10.000000 7.850000 78.500000 1.594000 2.772000
38 2021-01-05 00:00:00 2021-01-26 00:00:00 61.100000 61.100000 -61.100000 0.000000 17.530000 23.950000 19.620000 61.100000 7.970000 31 2020-12-31 00:00:00 694 Actual 11.000000 8.640000 78.500000 1.594000 2.772000
39 2020-11-30 00:00:00 2020-12-21 00:00:00 61.100000 64.230000 -64.230000 0.000000 17.530000 23.950000 19.620000 61.100000 7.970000 31 2020-11-30 00:00:00 683 Estimate 11.000000 8.640000 78.500000 1.594000 2.772000
40 2020-11-02 00:00:00 2020-11-23 00:00:00 64.230000 58.580000 -58.580000 0.000000 19.130000 26.110000 18.990000 64.230000 8.380000 30 2020-10-30 00:00:00 672 Actual 12.000000 9.420000 78.500000 1.594000 2.772000
42 2020-09-30 00:00:00 2020-10-21 00:00:00 58.580000 48.510000 -48.510000 0.000000 15.940000 21.760000 20.880000 58.580000 7.640000 33 2020-09-30 00:00:00 660 Estimate 10.000000 7.850000 78.500000 1.594000 2.772000
41 2020-08-31 00:00:00 2020-09-21 00:00:00 48.510000 56.690000 -56.690000 0.000000 12.750000 17.410000 18.350000 48.510000 6.330000 29 2020-08-28 00:00:00 650 Actual 8.000000 6.280000 78.500000 1.594000 2.772000
43 2020-07-30 00:00:00 2020-08-20 00:00:00 56.690000 49.150000 -49.150000 0.000000 15.940000 21.760000 18.990000 56.690000 7.390000 30 2020-07-30 00:00:00 642 Estimate 10.000000 7.850000 78.500000 1.594000 2.772000
45 2020-07-01 00:00:00 2020-07-22 00:00:00 49.150000 62.010000 -62.010000 0.000000 12.440000 16.980000 19.730000 49.150000 6.410000 32 2020-06-30 00:00:00 632 Actual 8.000000 6.280000 78.500000 1.555000 2.704000
46 2020-05-29 00:00:00 2020-06-19 00:00:00 62.010000 63.240000 -63.240000 0.000000 18.660000 25.470000 17.880000 62.010000 8.090000 29 2020-05-29 00:00:00 624 Estimate 12.000000 9.420000 78.500000 1.555000 2.704000
47 2020-05-01 00:00:00 2020-05-22 00:00:00 63.240000 70.600000 -70.600000 0.000000 18.660000 25.470000 19.110000 63.240000 8.250000 31 2020-04-30 00:00:00 612 Actual 12.000000 9.420000 78.500000 1.555000 2.704000
48 2020-03-30 00:00:00 2020-04-20 00:00:00 70.600000 57.720000 -57.720000 0.000000 21.770000 29.720000 19.110000 70.600000 9.210000 31 2020-03-30 00:00:00 600 Estimate 14.000000 10.990000 78.500000 1.555000 2.704000
49 2020-03-02 00:00:00 2020-03-23 00:00:00 57.720000 74.280000 -74.280000 0.000000 17.100000 23.360000 17.260000 57.720000 7.530000 28 2020-02-28 00:00:00 586 Actual 11.000000 8.640000 78.500000 1.555000 2.704000
50 2020-01-31 00:00:00 2020-02-21 00:00:00 74.280000 93.280000 -93.280000 0.000000 23.320000 31.850000 19.110000 74.280000 9.690000 31 2020-01-31 00:00:00 575 Estimate 15.000000 11.780000 78.500000 1.555000 2.704000
51 2020-01-03 00:00:00 2020-01-24 00:00:00 93.280000 54.660000 -54.660000 0.000000 31.100000 42.450000 19.730000 93.280000 12.170000 32 2019-12-31 00:00:00 560 Actual 20.000000 15.700000 78.500000 1.555000 2.704000
52 2019-12-05 00:00:00 2019-12-30 00:00:00 54.660000 0.000000 0.000000 0.000000 15.550000 21.230000 17.880000 54.660000 7.130000 29 2019-11-29 00:00:00 540 Estimate 10.000000 7.850000 78.500000 1.555000 2.704000
53 2019-11-05 00:00:00 2019-11-26 00:00:00 52.230000 66.940000 -66.940000 0.000000 14.000000 19.120000 19.110000 52.230000 6.810000 31 2019-10-31 00:00:00 530 Actual 9.000000 7.070000 78.500000 1.555000 2.704000
54 2019-10-02 00:00:00 2019-10-23 00:00:00 66.940000 58.960000 -58.960000 0.000000 20.220000 27.610000 19.110000 66.940000 8.730000 31 2019-09-30 00:00:00 521 Estimate 13.000000 10.210000 78.500000 1.555000 2.704000
56 2019-09-04 00:00:00 2019-09-25 00:00:00 58.960000 73.670000 -73.670000 0.000000 17.110000 23.360000 18.490000 58.960000 7.690000 30 2019-08-30 00:00:00 508 Actual 11.000000 8.640000 78.500000 1.555000 2.704000
57 2019-08-02 00:00:00 2019-08-23 00:00:00 73.670000 79.330000 -79.330000 0.000000 23.330000 31.850000 18.490000 73.670000 9.610000 30 2019-07-31 00:00:00 497 Estimate 15.000000 11.780000 78.500000 1.555000 2.704000
58 2019-07-04 00:00:00 2019-07-25 00:00:00 79.330000 67.930000 -67.930000 0.000000 25.810000 34.980000 18.540000 79.330000 10.350000 31 2019-07-01 00:00:00 482 Actual 16.450000 0.550000 78.500000 1.517000 2.618000
59 2019-06-05 00:00:00 2019-06-26 00:00:00 67.930000 76.860000 -76.860000 0.000000 21.240000 28.770000 17.920000 67.930000 8.860000 30 2019-05-31 00:00:00 465 Estimate 14.000000 10.990000 78.500000 1.517000 2.618000
In [4]:
df.describe()
Out[4]:
Invoice date Due date Total due ($) Opening balance ($) Payments received ($) Balance still owing ($) Water consumption ($) Wastewater consumption ($) Wastewater fixed ($) Balance of current charges ($) GST ($) Consumption period (days) Reading date Reading value (kL) Water consumption (kL) Wastewater consumption (kL) Wastewater rate (%) Water unit rate ($/kL) Wastewater unit rate ($/kL)
count 64 64 64.000000 64.000000 64.000000 64.000000 64.000000 64.000000 64.000000 64.000000 64.000000 64.000000 64 64.000000 64.000000 64.000000 64.0 64.000000 64.000000
mean 2022-01-15 21:22:30 2022-02-05 23:15:00 69.244219 59.806406 -62.052500 -2.246094 20.400781 27.845312 20.998125 69.244219 9.032187 30.500000 2022-01-14 10:07:30 835.015625 12.475781 9.601094 78.5 1.737016 3.020250
min 2019-06-05 00:00:00 2019-06-26 00:00:00 0.000000 -86.570000 -116.490000 -86.570000 -49.950000 -68.200000 14.210000 -86.570000 -11.290000 21.000000 2019-05-31 00:00:00 465.000000 3.000000 0.550000 78.5 1.517000 2.618000
25% 2020-09-22 12:00:00 2020-10-13 12:00:00 57.162500 57.162500 -73.772500 0.000000 15.940000 21.760000 18.527500 57.162500 7.457500 29.000000 2020-09-21 18:00:00 657.500000 9.000000 7.060000 78.5 1.594000 2.772000
50% 2022-01-19 00:00:00 2022-02-09 12:00:00 63.385000 63.385000 -63.385000 0.000000 18.455000 25.195000 19.990000 63.385000 8.265000 31.000000 2022-01-17 12:00:00 835.500000 11.000000 8.640000 78.5 1.706000 2.966000
75% 2023-05-08 06:00:00 2023-05-29 06:00:00 73.772500 73.772500 -57.162500 0.000000 21.802500 29.765000 22.950000 73.772500 9.622500 32.000000 2023-05-06 18:00:00 1027.000000 13.000000 10.200000 78.5 1.825000 3.174000
max 2024-09-06 00:00:00 2024-09-27 00:00:00 484.900000 116.490000 0.000000 0.000000 192.780000 263.240000 31.580000 484.900000 63.250000 40.000000 2024-09-03 00:00:00 1200.000000 90.000000 70.650000 78.5 2.142000 3.726000
std NaN NaN 56.223242 29.704873 20.942004 12.674220 24.183486 33.020630 3.312389 57.812202 7.540910 3.427248 NaN 207.889355 10.618429 8.404957 0.0 0.175876 0.307204
In [5]:
df.groupby(df["Invoice date"].dt.year)["Balance of current charges ($)"].sum()
Out[5]:
Invoice date
2019    453.72
2020    759.39
2021    764.92
2022    878.78
2023    795.04
2024    779.78
Name: Balance of current charges ($), dtype: float64
In [6]:
px.scatter(df, x="Reading date", y="Reading value (kL)", color="Reading type", title="Reading values (kL)", trendline="ols")
In [7]:
px.bar(df, x="Reading date", y="Water consumption (kL)")
In [8]:
df["Average daily usage (L)"] = df["Water consumption (kL)"] * 1000 / df["Consumption period (days)"]
px.bar(df, x="Reading date", y="Average daily usage (L)")
In [9]:
px.bar(df, x="Reading date", y="Balance of current charges ($)")
In [10]:
px.line(df, x="Reading date", y=["Water unit rate ($/kL)", "Wastewater unit rate ($/kL)"], title="Unit rates ($/kL)")